﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using ClosedXML.Excel;

namespace Office.Automatic.Core.Jobs
{
    internal class TempJob20230716: Job
    {
        private class Archive
        {
            public string Id { get; set; }
            public string Title { get; set; }
            public int Time { get; set; }
            public Archive(string id, string name, int time)
            {
                Id = id;
                Title = name;
                Time = time;
            }
        }

        private readonly Dictionary<string, List<Archive>> _nameToArchiveDict = new();

        public override string Name => "TempJob-20230716";
        public override bool IsTemporary => true;
        public override void Process()
        {
            LoadLibraryData(@"C:\Users\loyse\Desktop\Workspace\20230716\国税局干部人事档案2022.xlsx");
            //CheckStep01(@"C:\Users\loyse\Desktop\Workspace\20230716\2019年以来任新职级人员.xlsx");
            CheckStep03(@"C:\Users\loyse\Desktop\Workspace\20230716\2019年以来任新进人员.xlsx");
        }

        internal void LoadLibraryData(string path)
        {
            Console.WriteLine("Load archives data...");
            var cursorTop = Console.CursorTop;

            using var workbook = new XLWorkbook(path);

            var sheet = workbook.Worksheet("国税局干部人事档案2022年");
            var regex = new Regex("[\\u4e00-\\u9fa5,、（）0-9a-zA-Z]+（([\\u4e00-\\u9fa5]{2,})）$", RegexOptions.Multiline);
            if (sheet == null) return;

            var maxRowCount = sheet.LastRowUsed().RowNumber();

            for (var rowIndex = 2; rowIndex <= maxRowCount; rowIndex++)
            {
                Console.CursorTop = cursorTop;
                Console.CursorLeft = 0;
                Console.Write("Parse archive {0} of {1}...", (rowIndex - 1), maxRowCount - 1);

                if(sheet.Cell(rowIndex,9).Value.IsBlank) 
                    continue;

                var id = sheet.Cell(rowIndex, 9).Value.GetText();
                var title= sheet.Cell(rowIndex, 10).Value.GetText();
                var timeValue = sheet.Cell(rowIndex, 12).Value;

                var time = timeValue.IsNumber
                    ? Convert.ToInt32(timeValue.GetNumber())
                    : Convert.ToInt32(timeValue.GetText());

                string name;

                var match = regex.Match(title);
                if (match.Success)
                {
                    name = match.Groups[1].Value;
                }
                else
                    continue;

                if (_nameToArchiveDict.ContainsKey(name))
                {
                    _nameToArchiveDict[name].Add(new Archive(id, title, time));
                }
                else
                {
                    var list=new List<Archive>();
                    _nameToArchiveDict.Add(name,list);
                    list.Add(new Archive(id, title, time));
                }
            }

            Console.CursorTop = cursorTop;
            Console.CursorLeft = 0;
            Console.WriteLine("All {0} archives has been parsed.", maxRowCount - 1);
        }

        private void CheckStep01(string path)
        {
            Console.WriteLine("Check step 01 -->");

            using var workbook = new XLWorkbook(path);
            var sheet = workbook.Worksheet("20230716-职级查询");

            var maxRowCount= sheet.LastRowUsed().RowNumber();

            for (var rowIndex = 2; rowIndex < maxRowCount; rowIndex++)
            {
                var name = sheet.Cell(rowIndex, 1).Value.GetText();
                var time = sheet.Cell(rowIndex, 8).Value.GetText();
                var numTime = Convert.ToInt32(time.Substring(0, 4)) * 10000 +
                              Convert.ToInt32(time.Substring(5, 2)) * 100 + Convert.ToInt32(time.Substring(8, 2));

                if (!_nameToArchiveDict.ContainsKey(name))
                {
                    Console.WriteLine("Member named {0} not found in archives.", name);

                    sheet.Cell(rowIndex, 9).Value = "本级未编制档案";
                    continue;
                }

                var maxTime = Convert.ToInt32(time.Substring(0, 4)) * 10000 +
                              Convert.ToInt32(time.Substring(5, 2)) * 100 + 99;
                var minTime = Convert.ToInt32(time.Substring(0, 4)) * 10000 +
                              Convert.ToInt32(time.Substring(5, 2)) * 100;

                var archive01 = _nameToArchiveDict[name].FirstOrDefault(
                    t =>
                    {
                        var exp01 = t.Title.Contains("职级套转表") && numTime == 20190601;
                        var exp02 = t.Title.Contains("任免审批表");
                        var exp03 = t.Time > minTime && t.Time < maxTime;
                        return (exp01 || exp02) && exp03;
                    }
                );

                if (archive01 != null)
                {
                    sheet.Cell(rowIndex, 9).Value = archive01.Id;
                    sheet.Cell(rowIndex, 10).Value = archive01.Title;
                    sheet.Cell(rowIndex, 11).Value = archive01.Time;
                }
                else
                {
                    sheet.Cell(rowIndex, 9).Value = "缺少";
                }

                var archive02 = _nameToArchiveDict[name].FirstOrDefault(
                    t => t.Title.Contains("任前审核") && t.Time > minTime && t.Time < maxTime
                );

                if (archive02 != null)
                {
                    sheet.Cell(rowIndex, 12).Value = archive02.Id;
                    sheet.Cell(rowIndex, 13).Value = archive02.Title;
                    sheet.Cell(rowIndex, 14).Value = archive02.Time;
                }
                else
                {
                    if (numTime != 20190601)
                        sheet.Cell(rowIndex, 12).Value = "缺少";
                }

                if (numTime >= 20210520)
                    sheet.Cell(rowIndex, 15).Value = "缺少";

                Console.WriteLine("Member named {0} checked.", name);
            }

            workbook.Save();
        }

        private void CheckStep03(string path)
        {
            Console.WriteLine("Check step 03 -->");

            using var workbook = new XLWorkbook(path);
            var sheet = workbook.Worksheet("2019年以来新进人员");

            var maxRowCount = sheet.LastRowUsed().RowNumber();

            for (var rowIndex = 2; rowIndex < maxRowCount; rowIndex++)
            {
                var name = sheet.Cell(rowIndex, 1).Value.GetText();

                if (!_nameToArchiveDict.ContainsKey(name))
                {
                    Console.WriteLine("Member named {0} not found in archives.", name);

                    sheet.Cell(rowIndex, 8).Value = "本级未编制档案";
                    continue;
                }

                var archive01 = _nameToArchiveDict[name].FirstOrDefault(
                    t =>
                    {
                        var exp01 = t.Title.Contains("档案专项审核");
                        return exp01;
                    }
                );

                if (archive01 != null)
                {
                    sheet.Cell(rowIndex, 8).Value = archive01.Id;
                    sheet.Cell(rowIndex, 9).Value = archive01.Title;
                    sheet.Cell(rowIndex, 10).Value = archive01.Time;
                }
                else
                {
                    sheet.Cell(rowIndex, 8).Value = "缺少";
                }


                Console.WriteLine("Member named {0} checked.", name);
            }

            workbook.Save();
        }
    }
}
